OpenEdge Data Management:<br />DataServer for ODBC Error handling

One common data-entry error is attempting to add a duplicate record; that is, trying to create a record using a unique key that already exists in the database. Suppose, for example, that a user tries to add a customer with Cust-Num = 1 (where Cust-Num is a unique key), but a customer record with this Cust-Num value already exists. The attempt fails and Progress generates an error.

When this type of error occurs, Progress tries to resolve it by working back through the procedure, looking at each block header until it finds the closest block with the error-handling property, and then undoing and retrying the block. (See the OpenEdge Development: Progress 4GL Handbook for more information about error handling.) However, because the DataServer is accessing a non-OpenEdge data source, Progress cannot detect duplicate-key errors until the end of a transaction block. Therefore, if an error occurs in a subtransaction, Progress cannot detect it until the end of the entire transaction block and must perform default error handling for the entire transaction block.

rep-blk: REPEAT : PROMPT-FOR customer.cust-num. /* User input / FIND customer USING cust-num NO-ERROR. IF AVAILABLE customer THEN UPDATE customer.cust-num NAME customer.state. / User input / do-blk: DO ON ERROR UNDO do-blk, RETRY do-blk: FIND state WHERE st.state = customer.state. DISPLAY state. SET state. / User input */ END. END.

rep-blk: 
REPEAT : 
    PROMPT-FOR customer.cust-num. /* User input */ 
    FIND customer USING cust-num NO-ERROR. 
    IF AVAILABLE customer THEN 
        UPDATE customer.cust-num NAME customer.state. /* User input */ 
    do-blk: 
    DO ON ERROR UNDO do-blk, RETRY do-blk: 
        FIND state WHERE st.state = customer.state. 
        DISPLAY state. 
        SET state. /* User input */ 
    END. 
END.

This procedure displays the following screen, in which the user is prompted to enter data into the Cust-Num field and then the State field:

Suppose that the user enters an existing state (for example, NH) while Progress is processing the DO block. When this duplicate-key entry occurs for an OpenEdge database, Progress returns control to the DO block, displays a message that the record exists, and reprompts the user for a state abbreviation.

However, with the DataServer, if a duplicate key entry occurs in the DO block, Progress returns control to the REPEAT block rather than the DO block. As a result, the procedure reprompts the user for a customer number after the inner transaction completes:

If you use NO–ERROR to do your own error handling, you must account for the fact that an ODBC data source creates or updates a record later than Progress does. For example, the following code does not trap data-source errors, because the requests to perform the operations have not yet been sent to the data source:


CREATE customer NO-ERROR. ASSIGN cust-num = 45 NO-ERROR. ASSIGN name = "Smith" NO-ERROR.

The VALIDATE statement causes the DataServer to send requests to your ODBC data source, so incorporate it into your error-handling technique, as in the following example:

DEFINE VAR j AS INTEGER. DO TRANSACTION: CREATE customer NO-ERROR. ASSIGN cust-num = 45 NO-ERROR. VALIDATE customer. IF ERROR-STATUS:ERROR THEN DO: MESSAGE "error: number of messages = " ERROR-STATUS:NUM-MESSAGES. DO j = 1 TO ERROR-STATUS:NUM-MESSAGES: MESSAGE "error" ERROR-STATUS:GET-NUMBER(j) ERROR-STATUS:GET-MESSAGE (j). END. UNDO, LEAVE. END. ASSIGN name = "Smith" NO-ERROR. VALIDATE customer. IF ERROR-STATUS:ERROR THEN . . . END.

DEFINE VAR j AS INTEGER.
DO TRANSACTION:
  CREATE customer NO-ERROR.

  ASSIGN cust-num = 45 NO-ERROR.
  VALIDATE customer.
  IF ERROR-STATUS:ERROR THEN DO:
    MESSAGE "error: number of messages = " ERROR-STATUS:NUM-MESSAGES.
    DO j = 1 TO ERROR-STATUS:NUM-MESSAGES:
      MESSAGE "error" ERROR-STATUS:GET-NUMBER(j)
          ERROR-STATUS:GET-MESSAGE (j).
    END.
    UNDO, LEAVE.
  END.

  ASSIGN name = "Smith" NO-ERROR.
  VALIDATE customer.
  IF ERROR-STATUS:ERROR THEN . . .
END.

Another difference in behavior occurs when two users simultaneously attempt to create records with duplicate keys. Progress raises an error immediately, but the data source raises an error after the first transaction commits and only if the second transaction does not roll back. To avoid this difference, either change the scope of the transaction so that it completes more quickly or make the key nonunique and enforce uniqueness at the application level. Another technique is to use a RELEASE or VALIDATE statement when you check for the key’s uniqueness.